package com.bw.dao;

import com.bw.doman.sugar;
import com.bw.doman.sugarTable;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface sugarDao {
    @Select("SELECT sum(amount) value FROM order_info WHERE create_time>='2023-01-06 00:00:00' AND create_time<='2023-01-06 23:29:59'\n")
    double getList();


    @Select("CALL list\n")
    List<sugar> getList2();

    @Select(" SELECT a.*, rank() over(PARTITION by a.name ORDER BY a.distance) rak FROM(\n" +
            "SELECT o.org_name name,\n" +
            "sum(actual_distance) distance,\n" +
            "count(org_name) num,\n" +
            "sum(TIMESTAMPDIFF(SECOND,actual_start_time,actual_end_time)) time, \n" +
            "(sum(TIMESTAMPDIFF(SECOND,actual_start_time,actual_end_time))/count(org_name)) avgTime,\n" +
            "(sum(actual_distance)/count(org_name)) avgDistance\n" +
            "FROM transport_task t\n" +
            "LEFT JOIN line_base_info l on t.line_id=l.id\n" +
            "LEFT JOIN base_organ o on l.org_id=o.id\n" +
            "GROUP BY o.org_name)a")
    List<sugarTable> getList3();

    @Select("SELECT b2.name,count(t.sorter_emp_id) value FROM transport_task_detail t \n" +
            "LEFT JOIN transport_task t1 on t.transport_task_id=t1.id\n" +
            "LEFT JOIN line_base_info l on t1.line_id=l.id\n" +
            "LEFT JOIN base_organ b on l.org_id=b.id\n" +
            "LEFT JOIN base_region_info b1 on b.region_id=b1.id\n" +
            "LEFT JOIN base_region_info b2 on b1.parent_id=b2.id\n" +
            "GROUP BY b2.name")
    List<sugar> getList4();
}
